from settings import data1_connection
from util import float_to_percentage, time_stamp, select_online_bench, get_strategy_position, get_stock
import datetime
from pymysql.err import MySQLError
from strategy_backtest import backtest_netvalue_data
import time
import pandas as pd


class InsertStrategyBackest:

    def select_before_down(self, backtestId, date):
        """
        获取历史的最大的值百分比
        :param backtestId:
        :param date:
        :return:
        """
        cursor, conn = data1_connection()
        sql = "select accumuReturn, indexReturn from strategy_backtest_netvalue where dateTime < '%s' " \
              "and backtestId='%s'"
        end_sql = sql % (date, backtestId)
        try:
            cursor.execute(end_sql)
            conn.commit()
            data_list = cursor.fetchall()
            if data_list:
                max_accmu_return, max_index_return = \
                    self.get_accumuReturn_indexReturn_data_list(data_list)
                return max_accmu_return, max_index_return
            else:
                return 0, 0
        except MySQLError as e:
            print(e)
            conn.rollback()

    def insert_backtest_netvalue(self, backtestId):
        """
        向strategy_backtest_netvalue中写入数据
        :return:
        """

        cursor, conn = data1_connection()
        # TODO 第一次运行注释第二运行的data.second_data
        first = backtest_netvalue_data.first_data
        # TODO 第二次运行注释第一次运行的data.first_data
        # first = backtest_netvalue_data.second_data
        time_list = first["data"]["result"]["benchmark"]["time"]
        benchmark = first["data"]["result"]["benchmark"]["value"]
        over_all_return = first["data"]["result"]["overallReturn"]["value"]
        create_time = datetime.datetime.now().replace(microsecond=0)
        sql = "insert into strategy_backtest_netvalue (backtestId, bench," \
              "accumuReturn, indexReturn, drawdown, indexDrawdown, dateTime, createtime) " \
              "values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
        for index in range(len(benchmark)):
            time.sleep(0.3)
            date = time_stamp(time_list[index])
            date = date.date()
            bench = select_online_bench(date)
            if bench == None:
                bench = 0
            over_return = over_all_return[index]
            index_return = benchmark[index]
            # TODO 这里的backtestId随着你改变的回测的strateyg_backtest的id是改变的！
            backtestId = backtestId
            max_accumu_return, max_index_return = self.select_before_down(backtestId, date)
            if max_accumu_return != 0:
                # TODO 回测的回撤
                drawdown = abs(((float_to_percentage(over_return) + 1) / (max_accumu_return + 1)) - 1)
                # TODO 指数的回测
                indexDrawdown = abs(((float_to_percentage(index_return) + 1) / (max_index_return + 1)) - 1)
            else:
                drawdown = max_accumu_return
                indexDrawdown = max_index_return
            print("回撤为：%s, 指数的回测是：%s" % (drawdown, indexDrawdown))
            end_sql = sql % (backtestId, bench, float_to_percentage(over_return),
                             float_to_percentage(index_return), drawdown,
                             indexDrawdown, date, create_time)
            try:
                cursor.execute(end_sql)
                conn.commit()
                print("%s的%s收益曲线写入完毕！" % (backtestId, date))
            except MySQLError as e:
                print(e)
                conn.rollback()

    def update_other_data(self, backtestId):
        """
        等到数据添加完成的时候， 需要向数据库添加一些之前没法算出来的！
        :param backtestId:
        :return:
        """
        cursor, conn = data1_connection()
        # TODO 对应指数的年化收益
        # 拿到回测最新的一天的指数的收益的百分比
        sql = "select accumuReturn,indexReturn, dateTime from strategy_backtest_netvalue where " \
              "backtestId='%s' order by dateTime desc" % backtestId
        try:
            cursor.execute(sql)
            conn.commit()
            data = cursor.fetchall()
            index_return_list = list()
            if data:
                first_index_return = data[0]["indexReturn"]
                first_accumu_return = data[0]["accumuReturn"]
                second_index_return = data[1]["indexReturn"]
                second_accumu_return = data[1]["accumuReturn"]
                date_list = sorted([data[i]["dateTime"] for i in range(len(data))])
                for date in date_list:
                    for j in data:
                        if j["dateTime"] == date:
                            index_return = j["indexReturn"]
                            index_return_list.append(index_return)
                trade_days = len(data)
                # TODO 对应指数的年化收益
                indexAnnualReturn = pow((first_index_return + 1), (245 / trade_days)) - 1
                # TODO 指数对应的最大回撤
                indexMaxDrawdown = self.get_indexMaxDrawdown(index_return_list)
                # TODO 指数对应的波动率
                indexVolatility = ((first_index_return / second_index_return) - 1)
                # TODO 回测对应波动率
                volatility = ((first_accumu_return / second_accumu_return) - 1)
                try:
                    sql = "update strategy_backtest set volatility='%s' , indexAnnualReturn='%s', " \
                          "indexMaxDrawdown='%s', indexVolatility='%s' where id='%s'"
                    end_sql = sql % (volatility, indexAnnualReturn, indexMaxDrawdown, indexVolatility, backtestId)
                    cursor.execute(end_sql)
                    conn.commit()
                    print("更新回测的波动率， 指数的最大回撤数据， 指数的年化收益率， 指数的波动率！")
                except MySQLError as e:
                    conn.rollback()
                    print(e, "更新数据失败！")
        except MySQLError as e:
            conn.rollback()
            print('获取%s的回测的指数的收益百分比失败！' % backtestId)
            return

    def get_indexMaxDrawdown(self, data):
        indexReturn_list = []
        datas = [j + 1 for j in data]
        for i in range(len(datas)):
            indexReturn_list.append(abs((datas[i] / max(datas[:i+1])) - 1))
        max_index_return = max(indexReturn_list)
        return max_index_return

    def get_accumuReturn_indexReturn_data_list(self, data_list):
        """
        把查询数据库返回的数据， 转换为相应的列表， 再返回列表中的最大值
        :param data_list:
        :return:
        """
        accmu_reutrn_list = []
        index_return_list = []
        for data in data_list:
            accmuReutrn = data["accumuReturn"]
            indexReturn = data["indexReturn"]
            accmu_reutrn_list.append(accmuReutrn)
            index_return_list.append(indexReturn)
        max_accumu_return = max(accmu_reutrn_list)
        max_index_return = max(index_return_list)
        print("策略收益最大值%s：" % max_accumu_return)
        print("指数收益最大值%s:" % max_index_return)
        return max_accumu_return, max_index_return

    def insert_backtest_push(self, backtestId):
        """
        向线上数据库中写入回测数据的历史交易记录
        :return:
        """
        cursor, conn = data1_connection()
        error_index = []
        push_data = pd.read_csv("./push_csv/transaction_2.csv",
                                usecols=["日期", "时间", "标的", "交易类型", "成交数量", "成交价",
                                         "成交额", "成交状态"], encoding="gbk")

        sql = "insert into strategy_backtest_push (backtestId, sharesCode, sharesName, tradeTime, " \
              "direction, tradeNumber, tradePrice, tradeTotalPrice, createtime) value('%s', '%s', '%s', " \
              "'%s', '%s', '%s', '%s', '%s', '%s')"
        length = push_data.shape[0]
        for index in range(length):
            time.sleep(0.3)
            data_list = push_data.loc[index, :]
            data = list(data_list)
            if data[3] is "":
                print("下面的值为空！！！！")
                break
            # TODO backtestId 这里的id一定要进行修改哦， 不然的话有点难搞哦！
            backtestId = backtestId
            tradeTime = data[0] + " " + data[1]
            name_code = data[2]
            direction = data[3]
            status = data[7]
            if status != "全部成交":
                continue
            if direction == "买":
                direction = 1
            else:
                direction = -1
            amount = data[4]
            tradeNumber = get_strategy_position(amount)
            tradePrice = data[5]
            tradeTotalPrice = data[6]
            if isinstance(tradeTotalPrice, str):
                tradeTotalPrice = float(tradeTotalPrice.strip("()"))
            name, code = get_stock(name_code)
            createtime = datetime.datetime.now().replace(microsecond=0)
            end_sql = sql % (backtestId, code, name, tradeTime, direction, tradeNumber, tradePrice,
                             tradeTotalPrice, createtime)
            try:
                cursor.execute(end_sql)
                conn.commit()
                print("%s的%s交易记录写入成功" % (backtestId, tradeTime))
            except MySQLError as e:
                print(e)
                error_index.append(error_index.append(index))
                conn.rollback()


if __name__ == "__main__":
    insert_strategy_backtest = InsertStrategyBackest()
    # TODO 写入回测的收益曲线的数据
    # insert_strategy_backtest.insert_backtest_netvalue(2031)
    # TODO 写入回测的交易记录的数据
    # insert_strategy_backtest.insert_backtest_push(2031)
    # TODO 更新回测测相关数据， 传入对应的 backtest的Id， 切记
    for i in range(2001, 2032):
        print(i)
        try:
            insert_strategy_backtest.update_other_data(i)
        except Exception as e:
            raise e











































